﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Net;
using System.IO;
using Microsoft.Office.Tools.Excel;
using System.Threading;
using ConfigLibrary;
using System.Runtime.Serialization.Formatters.Binary;
using System.Runtime.InteropServices;


namespace Remodel
{
    public partial class spock : Form
    {
        WebBrowser spockload = new WebBrowser();        
        WebBrowser subpageload = new WebBrowser();
        WebBrowser eddswebpage = new WebBrowser();
        public static subpage newpage;
        public static eddsform eddspage;
        public static bool isopen = false;
        public static bool headerrow = false;
        HtmlElement table;
        HtmlElement qbuser;
        HtmlElement qbpass;
        HtmlElement signin;
        string eddsaddress;
        public static string address;
        HtmlElementCollection row;
        HtmlElementCollection cell;
        HtmlElementCollection rcell;
        HtmlElementCollection scell;
        HtmlElementCollection input;
        public static HtmlElement rec;
        public static HtmlElement str;
        public static HtmlElement seq;
        public static HtmlElement city;
        public static HtmlElement st;
        public static HtmlElement strtyp;
        Excel.Application xls;
        Excel.Workbook wbs;
        public static Excel.Worksheet wsc;
        Excel.Worksheet wsp;
        Excel.Range currentcell;
        Excel.Range previouscell;        
        Excel.Range del;
        DateTime dt;
        bool loggin = false;
        int rc;
        int cc;
        private static int lastrow;
        private static int lastcol;
        private static int lastrowp;
        private static int lastcolp;
        public static HashSet<DateTime> holidays = new HashSet<DateTime>();
        private static bool holidayrun = false;
        DateTime[] days = new DateTime[6];        
        //Config info
        private Config config;
        public static string email;
        public static string user;
        public static string pass;
        public static string password;
        private string host;
        private int port;
        private string current;
        private string previous;
        private int currrow;
        private int prevrow;
        public static string test;
        DataTable schedule;
        
        public spock()
        {
            InitializeComponent();
            emailpassword();
        }

        public void emailpassword()
        {
            Stream s = File.Open(Properties.Settings.Default.CONFIGTEST, FileMode.Open);
            BinaryFormatter b = new BinaryFormatter();
            config = (Config)b.Deserialize(s);
            s.Close();
            config.GetMailInfo(out host, out email, out password, out port);
            config.GetSqlInfo(out host, out user, out pass, out test); 
        }

        public void spock_Load(object sender, EventArgs e)
        {
            spockload.AllowNavigation = true;
            spockload.Navigate(new Uri("https://wmt.quickbase.com/db/bez7ue92u?a=q&qid=1003483", false));            
        }        
       
        private void spockload_DocumentCompleted(object sender, WebBrowserDocumentCompletedEventArgs e)
        {
            
            if (loggin == false)
            {
                qbuser = spockload.Document.GetElementById("loginid");
                qbuser.SetAttribute("value", user);
                qbpass = spockload.Document.GetElementById("password");
                qbpass.SetAttribute("value", pass);
                signin = spockload.Document.GetElementById("signin");
                signin.InvokeMember("click");
                loggin = true;
            }

            else if (loggin == true)
            {
                table = spockload.Document.All["VR_bez7ue92u_1003483"];
                spockload.Stop();
                spockload.Dispose();
                openxl();
                header();
                body();
                insertcolumnswsc();
                datecalcforA1toOps();
                datecalcforregscopeandshell();
                datecalcforqc();
                datecalcforregscopeotp();
                datecalcforregscopeaward();
                datecalcforcadclose();
                bldgportal();
                formatwsc();
                copycellswsc();
                compare();
                stoplightwsc();
                embeddedtable();
                formatwprc();
                printerwsc();
                copycellswprc();
                comparewprc();
                stoplightwprc();
                shutdown();
            }
        }

        private void bldgportal()
        {
            eddspage = new eddsform(wsc);            
            eddspage.Load += new EventHandler(eddspage_Load);
            eddspage.ShowDialog();            
        }

        void eddspage_Load(object sender, EventArgs e)
        {            
            eddswebpage.AllowNavigation = true;
            eddswebpage.Navigate("https://www.bldgportal.com/Login.aspx", false);            
        }

        private void openxl()
        {
            xls = new Excel.Application();
            xls.Visible = true;
            xls.DisplayAlerts = false;
            wbs = xls.Workbooks.Open("\\\\u:\\Reports\\Spock\\Spock.xlsx", 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            wsc = wbs.Worksheets.Add(Type.Missing, wbs.Worksheets[wbs.Worksheets.Count], Type.Missing, Type.Missing);
            wsp = wbs.Worksheets[wbs.Worksheets.Count - 1];            
            dt = DateTime.Today;
            string wsn = String.Format("{0:MM.dd.yy}", dt);
            wsc.Name = wsn;
            xls.DisplayAlerts = true;            
        }

        private void header()
        {
            cell = table.GetElementsByTagName("td");
            
                for (int c = 1; c < 37; c++)
                {                                   
                    HtmlElement cv = cell[c];
                    string cvstr = cv.InnerText;
                    wsc.Cells[1, c] = cvstr;                  
                }
                del = wsc.Range["E:E"];
                del.Delete();
                del = wsc.Range["E:E"];
                del.Delete();
                del = wsc.Range["L:L"];
                del.Delete();
                del = wsc.Range["S:S"];
                del.Delete();
        }

        private void body()
        {
            row = table.GetElementsByTagName("tr");
            rc = row.Count;
            string strtypname = " ";
            

            for (int r = 2; r < rc; r++)
            {
                rcell = row[r].GetElementsByTagName("td");
                cc = rcell.Count;

                for (int c = 1; c < cc; c++)
                {                    
                    HtmlElement td;
                    
                    if (c == 6)
                    {
                        HtmlElement strtyp = rcell[c];
                        strtypname = strtyp.InnerText;
                    }
                    if (c < 12)
                    {
                        td = rcell[c];
                        wsc.Cells[r - 1, c] = td.InnerText;
                    }                   
                    if (c == 12 && strtypname == "SAMS")
                    {                       
                        td = rcell[c];
                        wsc.Cells[r - 1, c -1] = td.InnerText;                       
                    }
                    
                    if (c > 12 && c < 20)
                    {
                        td = rcell[c];
                        wsc.Cells[r - 1, c - 1] = td.InnerText;
                    }
                    if (c == 20 && strtypname == "SAMS")
                    {                        
                        td = rcell[c];
                        wsc.Cells[r - 1, c - 2] = td.InnerText;                       
                    }                   
                    else if (c > 20)
                    {
                        td = rcell[c];
                        wsc.Cells[r - 1, c - 2] = td.InnerText;
                    }
                }
            }
        }

        private void insertcolumnswsc()
        {
            
            wsc.Range["L:L"].Insert();
            wsc.Range["L1"].Value = "A1 to Ops Proj";
            wsc.Range["O:O"].Insert();            
            wsc.Range["O1"].Value = "Reg Scope A1 & Shell Proj";
            wsc.Range["P1"].Value = "Reg Scope A1 & Shell Act";
            wsc.Range["U:U"].Insert();
            wsc.Range["U:U"].Insert();
            wsc.Range["U1"].Value = "QC Proj";
            wsc.Range["V1"].Value = "QC Act";
            wsc.Range["Y:Y"].Insert();
            wsc.Range["Y1"].Value = "Reg Scope OTP Proj";
            wsc.Range["AE1"].Value = "Contract Award Date";
            wsc.Range["AF:AF"].Insert();
            wsc.Range["AF1"].Value = "Reg Scope Award Proj";
            wsc.Range["AL1"].Value = "CAD Library Proj"; 
            wsc.Range["AM1"].Value = "CAD Library Act"; 
            wsc.Range["AN1"].Value = "100% $$ Closeout Proj";
            wsc.Range["AO1"].Value = "100% $$ Closeout Act";
            wsc.Range["AP1"].Value = "Admin";
            wsc.Range["AQ1"].Value = "Prj Arch";
            wsc.Range["AR1"].Value = "QC";
            wsc.Range["AS1"].Value = "Open Observ";
        }

        public static void datecalcforA1toOps()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            DateTime a1 = new DateTime();
            DateTime newdate = new DateTime();

            for (int a1r = 2; a1r < lastrow; a1r++)
            {
                if (wsc.Range["K" + a1r].Value is DateTime && wsc.Range["F" + a1r].Value == "SAMS")
                {
                    a1 = wsc.Range["K" + a1r].Value;
                    newdate = a1.AddDays(1);
                    int numberofdays = 4;
                    for (int d = numberofdays; d > 0; d--)
                    {
                        newdate = newdate.AddDays(1);
                        int holidayyear = newdate.Year;
                        int holidaylastyear = holidayyear - 1;
                        int holidaynextyear = holidayyear + 1;
                        if (holidayrun == false)
                        {
                            GetHolidays(holidaylastyear);
                            GetHolidays(holidayyear);
                            GetHolidays(holidaynextyear);
                            holidayrun = true;
                        }

                        if (newdate.DayOfWeek == DayOfWeek.Sunday || newdate.DayOfWeek == DayOfWeek.Saturday || holidays.Contains(newdate))
                        {
                            d++;
                        }                        
                    }
                    wsc.Range["L" + a1r].Value = newdate;
                }
            }
        }

        public static void datecalcforregscopeandshell()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            DateTime rss = new DateTime();
            DateTime newdate = new DateTime();

            for (int a1r = 2; a1r < lastrow; a1r++)
            {
                if (wsc.Range["N" + a1r].Value is DateTime && wsc.Range["F" + a1r].Value == "SAMS")
                {
                    rss = wsc.Range["N" + a1r].Value;
                    if (rss.DayOfWeek == DayOfWeek.Sunday)
                    {
                        newdate = rss.AddDays(2);
                    }
                    else if (rss.DayOfWeek == DayOfWeek.Saturday || rss.DayOfWeek == DayOfWeek.Friday)
                    {
                        newdate = rss.AddDays(3);
                    }                    
                    else
                    {
                        newdate = rss.AddDays(1);
                    }
                    
                    int numberofdays = 4;
                    for (int d = numberofdays; d > 0; d--)
                    {
                        newdate = newdate.AddDays(1);                       
                        
                        if (newdate.DayOfWeek == DayOfWeek.Sunday || newdate.DayOfWeek == DayOfWeek.Saturday || holidays.Contains(newdate))
                        {
                            d++;
                        }                        
                    }
                    wsc.Range["O" + a1r].Value = newdate;
                }
            }
        }

        public static void datecalcforqc()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            DateTime otp = new DateTime();
            DateTime newdate = new DateTime();
            
            for (int otpr = 2; otpr < lastrow; otpr++)
            {                  
                if (wsc.Range["W" + otpr].Value is DateTime)
                {                        
                        otp = wsc.Range["W" + otpr].Value;
                        newdate = otp.AddDays(-1);
                        int numberofdays = 9;
                        for (int d = numberofdays; d > 0; d--)
                        {
                            newdate = newdate.AddDays(-1);
                           
                            if (newdate.DayOfWeek == DayOfWeek.Sunday || newdate.DayOfWeek == DayOfWeek.Saturday || holidays.Contains(newdate))
                            {
                                d++;
                            }
                        }
                        wsc.Range["U" + otpr].Value = newdate;                    
                }
            }            
        }

        public static void datecalcforregscopeotp()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            DateTime a1 = new DateTime();
            DateTime newdate = new DateTime();

            for (int a1r = 2; a1r < lastrow; a1r++)
            {
                if (wsc.Range["X" + a1r].Value is DateTime && wsc.Range["F" + a1r].Value == "SAMS")
                {
                    a1 = wsc.Range["X" + a1r].Value;
                    newdate = a1.AddDays(1);
                    int numberofdays = 5;
                    for (int d = numberofdays; d > 0; d--)
                    {
                        newdate = newdate.AddDays(1);
                        
                        if (newdate.DayOfWeek == DayOfWeek.Sunday || newdate.DayOfWeek == DayOfWeek.Saturday || holidays.Contains(newdate))
                        {
                            d++;
                        }                        
                    }
                    wsc.Range["Y" + a1r].Value = newdate;
                }
            }
        }

        public static void datecalcforregscopeaward()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            DateTime a1 = new DateTime();
            DateTime newdate = new DateTime();

            for (int a1r = 2; a1r < lastrow; a1r++)
            {
                if (wsc.Range["AE" + a1r].Value is DateTime && wsc.Range["F" + a1r].Value == "SAMS")
                {
                    a1 = wsc.Range["AE" + a1r].Value;
                    newdate = a1.AddDays(1);
                    int numberofdays = 5;
                    for (int d = numberofdays; d > 0; d--)
                    {
                        newdate = newdate.AddDays(1);                        

                        if (newdate.DayOfWeek == DayOfWeek.Sunday || newdate.DayOfWeek == DayOfWeek.Saturday || holidays.Contains(newdate))
                        {
                            d++;
                        }                        
                    }
                    wsc.Range["AF" + a1r].Value = newdate;
                }
            }
        }

        private static HashSet<DateTime> GetHolidays(int year)
        {
            //holidays = new HashSet<DateTime>();
            //NEW YEARS 
            DateTime newYearsDate = AdjustForWeekendHoliday(new DateTime(year, 1, 1).Date);
            holidays.Add(newYearsDate);
            //MEMORIAL DAY  -- last monday in May 
            DateTime memorialDay = new DateTime(year, 5, 31);
            DayOfWeek dayOfWeek = memorialDay.DayOfWeek;
            while (dayOfWeek != DayOfWeek.Monday)
            {
                memorialDay = memorialDay.AddDays(-1);
                dayOfWeek = memorialDay.DayOfWeek;
            }
            holidays.Add(memorialDay.Date);

            //INDEPENDENCE DAY 
            DateTime independenceDay = AdjustForWeekendHoliday(new DateTime(year, 7, 4).Date);
            holidays.Add(independenceDay);

            //LABOR DAY -- 1st Monday in September 
            DateTime laborDay = new DateTime(year, 9, 1);
            dayOfWeek = laborDay.DayOfWeek;
            while (dayOfWeek != DayOfWeek.Monday)
            {
                laborDay = laborDay.AddDays(1);
                dayOfWeek = laborDay.DayOfWeek;
            }
            holidays.Add(laborDay.Date);

            //THANKSGIVING DAY - 4th Thursday in November 
            var thanksgiving = (from day in Enumerable.Range(1, 30)
                                where new DateTime(year, 11, day).DayOfWeek == DayOfWeek.Thursday
                                select day).ElementAt(3);
            DateTime thanksgivingDay = new DateTime(year, 11, thanksgiving);
            holidays.Add(thanksgivingDay.Date);

            //CHRISTMAS DAY
            DateTime christmasDay = AdjustForWeekendHoliday(new DateTime(year, 12, 25).Date);
            holidays.Add(christmasDay);
            return holidays;
        }

        public static DateTime AdjustForWeekendHoliday(DateTime holiday)
        {
            if (holiday.DayOfWeek == DayOfWeek.Saturday)
            {
                return holiday.AddDays(-1);
            }
            else if (holiday.DayOfWeek == DayOfWeek.Sunday)
            {
                return holiday.AddDays(1);
            }
            else
            {
                return holiday;
            }
        }

        private void datecalcforcadclose()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;

            for (cc = 2; cc < lastrow; cc++)
            {
                if (wsc.Range["AK" + cc].Value is DateTime)
                {
                    DateTime go = wsc.Range["AK" + cc].Value;                    
                    DateTime close = go.AddDays(90);
                    DateTime cad = go.AddDays(30);                    
                    wsc.Range["AL" + cc].Value = cad;
                    wsc.Range["AN" + cc].Value = close;
                }
            }
        }        

        private void compare()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcol = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            int wsclr = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int wsclc = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            int wsplr = wsp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;            
            int cr;
            int pr;
            int cc;            
            int cid = 0;
            int pid = 0;
            int crow = 2;

            for (cr = 2; cr < wsclr; cr++)
            {
                bool found = false;
                
                if (wsc.Range["A" + cr].Value > 0)
                {
                    cid = (int)wsc.Range["A" + cr].Value;
                }                

                for (pr = crow; pr < wsplr; pr++)
                {
                    if (wsp.Range["A" + pr].Value > 0)
                    {
                        pid = (int)wsp.Range["A" + pr].Value;
                    }

                    if (pid == cid)
                    {
                        crow = pr;
                        found = true;
                            
                        for (cc = 1; cc < wsclc; cc++)
                        {
                            currentcell = wsc.Cells[cr, cc];                            
                            previouscell = wsp.Cells[pr, cc];

                            if (wsc.Cells[cr, cc].Value is string  && cc >= 11)
                            {
                                string na = wsc.Cells[cr, cc].Value;
                                int nal = na.Length;
                                if (nal > 5)
                                {
                                    string subna = na.Substring(na.Length - 4);
                                    if (subna == "1111")
                                    {
                                        wsc.Cells[cr, cc].Value = "N/A";
                                    }
                                }
                                else if (wsc.Cells[cr, cc].Value == "n/a" || wsc.Cells[cr, cc].Value == "na")
                                {
                                    wsc.Cells[cr, cc].Value = "N/A";
                                }
                            }                           
                            
                            if (currentcell.Value is DateTime && previouscell.Value is string)
                            {
                                currentcell.Interior.Color = Color.Goldenrod;
                                wsc.Range["A" + cr].Interior.Color = Color.Goldenrod;
                            }
                            else if (currentcell.Value is string && previouscell.Value is DateTime)
                            {
                                currentcell.Interior.Color = Color.Goldenrod;
                                wsc.Range["A" + cr].Interior.Color = Color.Goldenrod;
                            }
                            else if (currentcell.Value != previouscell.Value)
                            {
                                currentcell.Interior.Color = Color.Goldenrod;
                                wsc.Range["A" + cr].Interior.Color = Color.Goldenrod;
                            }
                        }
                        break;  
                    }
                    //else if (pr == wsplr - 1 && found == false)
                    //{
                    //    wsc.Range["A" + cr + ":" + "AS" + cr].Interior.Color = Color.Goldenrod;
                    //    break;
                    //}
                 }                
            }
        }

        private void stoplightwsc()
        {
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcol = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            DateTime today = DateTime.Today;
            DataTable red = new DataTable();
            DataTable pink = new DataTable();
            DataTable yellow1 = new DataTable();
            DataTable yellow2 = new DataTable();
            DataTable green3 = new DataTable();
            DataTable green4 = new DataTable();
            
            int l = days.Length;
            DateTime newdate = new DateTime();

            for (int i = 0; i < l; i++)
            {
                if (i == 0)
                {
                    days[i] = today.AddDays(-1);
                    if (days[i].DayOfWeek == DayOfWeek.Sunday)
                    {
                        days[i] = days[i].AddDays(-2);
                        if (holidays.Contains(days[i]))
                        {
                            days[i] = days[i].AddDays(-1);
                        }
                    }
                    else if (holidays.Contains(days[i]))
                    {
                        days[i] = days[i].AddDays(1);
                    }
                    
                }
                else
                {
                    days[i] = newdate;

                    if (i == 1)
                    {
                        days[i] = today;
                    }

                    else if (i == 2)
                    {
                        days[i] = today.AddDays(1);
                        newdate = days[i];

                        if (days[i].DayOfWeek == DayOfWeek.Saturday)
                        {
                            days[i] = days[i].AddDays(2);
                            newdate = days[i];

                            if (holidays.Contains(newdate))
                            {
                                days[i] = days[i].AddDays(1);
                                newdate = days[i];
                            }
                        }

                        else if (holidays.Contains(newdate))
                        {
                            days[i] = days[i].AddDays(1);
                            newdate = days[i];
                        }
                    }

                    else
                    {
                        days[i] = newdate.AddDays(1);
                        newdate = days[i];

                        if (days[i].DayOfWeek == DayOfWeek.Saturday)
                        {
                            days[i] = days[i].AddDays(2);
                            newdate = days[i];

                            if (holidays.Contains(days[i]))
                            {
                                days[i] = days[i].AddDays(1);
                                newdate = days[i];
                            }
                        }

                        else if (holidays.Contains(days[i]))
                        {
                            days[i] = days[i].AddDays(1);
                            newdate = days[i];
                        }
                    }
                }                
            }    

            for (int i = 2; i < lastrow; i++)
            {
                schedule = new DataTable();
                schedule.Columns.Add("strnum");
                schedule.Columns.Add("strseq");
                schedule.Columns.Add("city");
                schedule.Columns.Add("st");
                schedule.Columns.Add("task");
                schedule.Columns.Add("resource");
                
                
                for (int c = 11; c < lastcol; c++)
                {
                    if (wsc.Cells[i, c].Value is string)
                    {
                        string na = wsc.Cells[i, c].Value;
                        int nal = na.Length;
                        if (nal > 5)
                        {
                            string subna = na.Substring(na.Length - 4);
                            if (subna == "1111")
                            {
                                wsc.Cells[i, c].Value = "N/A";
                            }
                        }
                        else if (wsc.Cells[i, c].Value == "n/a" || wsc.Cells[i, c].Value == "na")
                        {
                            wsc.Cells[i, c].Value = "N/A";
                        }
                    }              
                    
                    if (wsc.Cells[i, c].Value is DateTime)
                    {

                        if (c == 13 || c == 16 || c == 18 || c == 20 || c == 22 || c == 24 || c == 26 || c == 28 || c == 30 || c == 33 || c == 39 || c == 41)
                        {
                            continue;
                        }

                        if ((c == 12 || c == 15 || c == 17 || c == 19 || c == 21 || c == 23 || c == 25 || c == 27 || c == 29 || c == 32 || c ==38 || c == 40) && wsc.Cells[i, 10].Value != " ")
                        {
                            string test = wsc.Cells[i, 10].Value;
                            string str = Convert.ToString(wsc.Cells[i, 2].Value);
                            string seq = Convert.ToString(wsc.Cells[i, 3].Value);
                            string city = Convert.ToString(wsc.Cells[i, 4].Value);
                            string st = Convert.ToString(wsc.Cells[i, 5].Value);
                            string strtyp = Convert.ToString(wsc.Cells[i, 6].Value);
                            

                            Excel.Range header = wsc.Cells[1, c];
                            string task = header.Value;

                            if (wsc.Cells[i, c].Value <= days[0] && !(wsc.Cells[i, c + 1].Value is DateTime))
                            {
                                if (wsc.Cells[i, c + 1].Value == "N/A")
                                {}
                                else
                                {
                                    Excel.Range r = wsc.Cells[i, c + 1];
                                    r.Interior.Color = Color.Red;
                                    wsc.Range["B" + i].Interior.Color = Color.Red;
                                    DataRow addedtask = schedule.NewRow();

                                }
                            }

                            else if (wsc.Cells[i, c].Value == days[1] && !(wsc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range p = wsc.Cells[i, c];
                                p.Interior.Color = Color.HotPink;
                                wsc.Range["B" + i].Interior.Color = Color.HotPink;
                            }

                            else if (wsc.Cells[i, c].Value == days[2] && !(wsc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range y = wsc.Cells[i, c];
                                y.Interior.Color = Color.Yellow;
                                wsc.Range["B" + i].Interior.Color = Color.Yellow;
                            }

                            else if (wsc.Cells[i, c].Value == days[3] && !(wsc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range y = wsc.Cells[i, c];
                                y.Interior.Color = Color.Yellow;
                                wsc.Range["B" + i].Interior.Color = Color.Yellow;
                            }

                            else if (wsc.Cells[i, c].Value == days[4] && !(wsc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range g = wsc.Cells[i, c];
                                g.Interior.Color = Color.LimeGreen;
                                wsc.Range["B" + i].Interior.Color = Color.LimeGreen;
                            }

                            else if (wsc.Cells[i, c].Value == days[5] && !(wsc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range g = wsc.Cells[i, c];
                                g.Interior.Color = Color.LimeGreen;
                                wsc.Range["B" + i].Interior.Color = Color.LimeGreen;
                            }
                        }

                        else
                        {
                            if ((DateTime)wsc.Cells[i, c].Value == days[1])
                            {
                                Excel.Range p = wsc.Cells[i, c];
                                p.Interior.Color = Color.HotPink;
                            }

                            else if ((DateTime)wsc.Cells[i, c].Value == days[2])
                            {
                                Excel.Range y = wsc.Cells[i, c];
                                y.Interior.Color = Color.Yellow;
                            }

                            else if ((DateTime)wsc.Cells[i, c].Value == days[3])
                            {
                                Excel.Range y = wsc.Cells[i, c];
                                y.Interior.Color = Color.Yellow;
                            }

                            else if ((DateTime)wsc.Cells[i, c].Value == days[4])
                            {
                                Excel.Range g = wsc.Cells[i, c];
                                g.Interior.Color = Color.LimeGreen;
                            }

                            else if ((DateTime)wsc.Cells[i, c].Value == days[5])
                            {
                                Excel.Range g = wsc.Cells[i, c];
                                g.Interior.Color = Color.LimeGreen;
                            }
                        }
                    }                    
                }
            }
            xls.ScreenUpdating = true;
            xls.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
        }

        private void message()
        {
            string preamble = "";
            string postscript = "";
            string table = @"<TABLE border=""0"" cellpadding=""1"">
                                <TR>
                                    <TD bgcolor=""#FFFFFF"" align=""center"" width=""500"" valign=""middle"">
                                        <TABLE border=""0"" cols=""1"" style=""font-size: 9pt; color: black; font-family: tahoma, helvetica"" width=""100%"" cellspacing=""1"" cellpadding=""0"">";
            preamble = "Here is the schedule for this week:";


        }


        private void formatwsc()
        {
            wsc.Application.ActiveWindow.SplitRow = 1;
            wsc.Application.ActiveWindow.SplitColumn = 5;
            wsc.Application.ActiveWindow.FreezePanes = true;            
            lastrow = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcol = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            Excel.Range rng = wsc.Range["A1:AS" + lastrow];
            rng.Font.Name = "Tahoma";
            rng.Font.Size = 8;
            rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            rng.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;             
            Excel.Range dates = wsc.Range["K2:AO" + lastrow];
            dates.NumberFormat = "mm/dd/yy";
            Excel.Range center = wsc.Range["A:C,E:H,J:AO,AS:AS"];
            center.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            Excel.Range left = wsc.Range["D:D,AP:AR"];
            left.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            Excel.Range txt = wsc.Range["A:A,AS:AS"];
            txt.ColumnWidth = 6;
            Excel.Range strseq = wsc.Range["B:C"];
            strseq.ColumnWidth = 4.57;
            Excel.Range cty = wsc.Range["D:D"];
            cty.ColumnWidth = 13;
            Excel.Range st = wsc.Range["E:E"];
            st.ColumnWidth = 2.71;
            Excel.Range typyr = wsc.Range["F:H"];
            typyr.ColumnWidth = 4.71;
            Excel.Range sap = wsc.Range["J:J"];
            sap.ColumnWidth = 11;
            Excel.Range dte = wsc.Range["I:I,K:AO"];
            dte.ColumnWidth = 7;
            Excel.Range names = wsc.Range["AP:AR"];
            names.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            names.ColumnWidth = 9;
            Excel.Range header = wsc.Range["A1:AS1"];
            Excel.Range rows = wsc.Range["1:" + lastrow];
            rows.RowHeight = 12.75;
            header.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
            header.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
            header.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
            header.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
            header.WrapText = true;
            header.Font.Bold = true;
            header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            header.RowHeight = 60;
            wsc.PageSetup.PrintTitleRows = "$A$1:$AS$1";
        }

        private void printerwsc()
        {
            wsc.PageSetup.PrintArea = "";
            wsc.PageSetup.CenterHeader = "";
            wsc.PageSetup.CenterHeader = "&\"Tahoma,Bold\"& Spock Report dated " + wsc.Name;
            wsc.PageSetup.LeftMargin = xls.InchesToPoints(0.1);
            wsc.PageSetup.RightMargin = xls.InchesToPoints(0.1);
            wsc.PageSetup.TopMargin = xls.InchesToPoints(0.65);
            wsc.PageSetup.BottomMargin = xls.InchesToPoints(0.29);
            wsc.PageSetup.HeaderMargin = xls.InchesToPoints(0.36);
            wsc.PageSetup.FooterMargin = xls.InchesToPoints(0.25);
            wsc.PageSetup.PrintTitleRows = "$1:$1";
            wsc.PageSetup.PrintQuality = 600;
            wsc.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
            wsc.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17;
            wsc.PageSetup.Zoom = 53;
        }

        private void copycellswsc()
        {
            int wsclr = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int wsplr = wsp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int c;
            int p;            
            int cid;
            int pid;

            for (c = 2; c < wsclr; c++)
            {
                cid = (int)wsc.Range["A" + c].Value;
                bool found = false;

                for (p = 2; p < wsplr; p++)
                {
                    pid = (int)wsp.Range["A" + p].Value;                    

                    if (cid == pid)
                    {
                        wsc.Range["V" + c].Value = wsp.Range["V" + p].Value;
                        wsc.Range["AM" + c].Value = wsp.Range["AM" + p].Value;
                        wsc.Range["AO" + c].Value = wsp.Range["AO" + p].Value;
                        wsc.Range["AP" + c].Value = wsp.Range["AP" + p].Value;
                        wsc.Range["AQ" + c].Value = wsp.Range["AQ" + p].Value;
                        wsc.Range["AR" + c].Value = wsp.Range["AR" + p].Value;
                        found = true;
                        break;
                    }

                    else if (p == wsplr - 1 && found == false)  
                    {
                        Excel.Range newstr = wsc.Range["A" + c + ":AS" + c];
                        newstr.Interior.Color = Color.Goldenrod;
                    }
                }                
            }
        }

        void newpage_Load(object sender, EventArgs e)
        {
            subpageload.AllowNavigation = true;
            subpageload.Navigate(new Uri(address, false));
        }

        private void embeddedtable()
        {
            for (int r = 3; r < rc; r++) //r < rc            
            {               
                rcell = row[r].GetElementsByTagName("td");
                HtmlElement subtablename = rcell[0];
                rec = rcell[1];
                str = rcell[2];
                seq = rcell[3];
                city = rcell[4];
                st = rcell[5];
                strtyp = rcell[6];
                string subtable = subtablename.InnerHtml;
                string[] target = subtable.Split(new Char[] { '"' });
                string revised = target[5].Replace("&amp;", "&");
                address = "https://wmt.quickbase.com/db/" + revised;                
                newpage = new subpage();
                newpage.Load += new EventHandler(newpage_Load);                
                newpage.ShowDialog();
            }
        }

        private void formatwprc()
        {
            subpage.wprc.Application.ActiveWindow.SplitRow = 1;
            subpage.wprc.Application.ActiveWindow.FreezePanes = true;
            lastrow = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcol = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            Excel.Range rng = subpage.wprc.Range["A1:N" + lastrow];
            rng.Font.Name = "Tahoma";
            rng.Font.Size = 8;
            rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            rng.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
            rng.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            rng.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            Excel.Range dates = subpage.wprc.Range["H2:K" + lastrow];
            dates.NumberFormat = "mm/dd/yy";
            Excel.Range center = subpage.wprc.Range["A:C,E:J"];
            center.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            Excel.Range left = subpage.wprc.Range["D:D,L:N"];
            left.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            Excel.Range txt = subpage.wprc.Range["A:A"];
            txt.ColumnWidth = 6;
            Excel.Range strseq = subpage.wprc.Range["B:C"];
            strseq.ColumnWidth = 4.57;
            Excel.Range cty = subpage.wprc.Range["D:D"];
            cty.ColumnWidth = 13;
            Excel.Range st = subpage.wprc.Range["E:E"];
            st.ColumnWidth = 2.71;                  
            Excel.Range dte = subpage.wprc.Range["F:J"];
            dte.ColumnWidth = 8;
            Excel.Range names = subpage.wprc.Range["K:N"];
            names.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            names.ColumnWidth = 9;
            Excel.Range header = subpage.wprc.Range["A1:N1"];
            header.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;
            header.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;
            header.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;
            header.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
            header.WrapText = true;
            header.Font.Bold = true;
            header.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            header.RowHeight = 39;
        }

        private void copycellswprc()
        {
            int wprclr = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int wprplr = subpage.wprp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int wsclr = wsc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            int c;
            int p;
            int crow = 2;
            int srow = 2;
            int cid = 0;
            string ptc;
            int pid = 0;
            string ptp;
            string admin = " ";
            string prjarch = " ";
            string qc = " ";

            for (c = 2; c < wprclr; c++)// change to start @ 2
            {
                if (subpage.wprc.Range["A" + c].Value > 0)
                {
                    cid = (int)subpage.wprc.Range["A" + c].Value;
                }
                ptc = subpage.wprc.Range["G" + c].Value;                
                bool found = false;
                
                for (p = crow; p < wprplr; p++)
                {
                    if (subpage.wprp.Range["A" + p].Value > 0)
                    {
                        pid = (int)subpage.wprp.Range["A" + p].Value;
                        if (pid != cid && p != wprplr - 1)
                        {
                            continue;
                        }
                        else if (pid == cid)
                        {
                            crow = p;
                        }
                    }
                    ptp = subpage.wprp.Range["G" + p].Value;

                    if ((cid + ptc) == (pid + ptp))
                    {
                        for (int q = srow; q < wsclr; q++)
                        {
                            int sid = (int)wsc.Range["A" + q].Value;
                            if (sid == cid)
                            {
                                srow = q;
                                string admin_curr = subpage.wprc.Range["L" + c].Value;
                                string prjarch_curr = subpage.wprc.Range["M" + c].Value;
                                string qc_curr = subpage.wprc.Range["N" + c].Value;
                                string admin_prev = subpage.wprp.Range["L" + p].Value;
                                string prjarch_prev = subpage.wprp.Range["M" + p].Value;
                                string qc_prev = subpage.wprp.Range["N" + p].Value;
                                admin = wsc.Range["AP" + q].Value;
                                prjarch = wsc.Range["AQ" + q].Value;
                                qc = wsc.Range["AR" + q].Value;
                                
                                if (String.IsNullOrEmpty(admin_curr))
                                {
                                    if (!String.IsNullOrEmpty(admin_prev))  //subpage.wprp.Range["L" + p].Value != " "
                                    {
                                        subpage.wprc.Range["L" + c].Value = subpage.wprp.Range["L" + p].Value;
                                    }
                                    else subpage.wprc.Range["L" + c].Value = admin;                                  
                                }

                                if (String.IsNullOrEmpty(prjarch_curr))
                                {
                                    if (!String.IsNullOrEmpty(prjarch_prev))  //subpage.wprp.Range["M" + p].Value != " " 
                                    {
                                        subpage.wprc.Range["M" + c].Value = subpage.wprp.Range["M" + p].Value;
                                    }
                                    else subpage.wprc.Range["M" + c].Value = prjarch;                                    
                                }

                                if (String.IsNullOrEmpty(qc_curr))
                                {
                                    if (!String.IsNullOrEmpty(qc_prev))  //subpage.wprp.Range["N" + p].Value != " "
                                    {
                                        subpage.wprc.Range["N" + c].Value = subpage.wprp.Range["N" + p].Value;
                                        break;
                                    }
                                    else subpage.wprc.Range["N" + c].Value = qc;
                                    break;
                                }
                            }
                        }                                            
                        found = true;
                        break;
                    }

                    else if (p == wprplr - 1 && found == false) 
                    {
                        Excel.Range newstr = subpage.wprc.Range["A" + c + ":N" + c];
                        newstr.Interior.Color = Color.Goldenrod;
                        subpage.wprc.Range["L" + c].Value = admin;
                        subpage.wprc.Range["M" + c].Value = prjarch;
                        subpage.wprc.Range["N" + c].Value = qc;
                        for (int z = 8; z < 12; z++)
                        {
                            currentcell = subpage.wprc.Cells[c, z];
                            if (currentcell.Value != null)
                            {
                                current = Convert.ToString(currentcell.Value);
                                int nal = current.Length;
                                if (nal > 5)
                                {
                                    string subna = current.Substring(current.Length - 4);
                                    if (subna == "1111")
                                    {
                                        currentcell.Value = "N/A";
                                        current = Convert.ToString(currentcell.Value);
                                    }
                                }
                                else if (current == "n/a" || current == "na")
                                {
                                    currentcell.Value = "N/A";
                                    current = Convert.ToString(currentcell.Value);
                                }
                            }
                        }
                        break;
                    }
                }
            }
        }

        private void comparewprc()
        {
            lastrow = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcol = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            lastrowp = subpage.wprp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcolp = subpage.wprp.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;
            int cprr;
            int pprr;
            int cprc;
            int prcid = 0;
            int prpid = 0;
            string idptc = " ";
            string idptp = " ";
            int crow = 2;

            for (cprr = 2; cprr < lastrow; cprr++)  
            {
                bool foundid = false;

                if (subpage.wprc.Range["A" + cprr].Value > 0)
                {
                    prcid = (int)subpage.wprc.Range["A" + cprr].Value;
                    idptc = Convert.ToString(prcid) + subpage.wprc.Range["G" + cprr].Value;
                }
                else
                {
                    idptc = Convert.ToString(prcid) + subpage.wprc.Range["G" + cprr].Value;
                }

                for (pprr = crow; pprr < lastrowp; pprr++)
                {
                    if (subpage.wprp.Range["A" + pprr].Value > 0)
                    {
                        prpid = (int)subpage.wprp.Range["A" + pprr].Value;
                        idptp = Convert.ToString(prpid) + subpage.wprp.Range["G" + pprr].Value;
                        if (prpid != prcid)
                        {
                            continue;
                        }
                        else if (prpid == prcid)
                        {
                            crow = pprr;
                        }
                    }
                    else
                    {
                        idptp = Convert.ToString(prpid) + subpage.wprp.Range["G" + pprr].Value;
                    }

                    if (idptc == idptp)
                    {
                        foundid = true;                        

                        for (cprc = 8; cprc < lastcol; cprc++)
                        {
                            if (subpage.wprc.Range["A" + cprr].Value > 0)
                            {
                                currentcell = subpage.wprc.Cells[cprr, cprc];
                                currrow = currentcell.Row;
                                previouscell = subpage.wprp.Cells[pprr, cprc];
                                prevrow = previouscell.Row;
                                current = Convert.ToString(currentcell.Value);
                                previous = Convert.ToString(previouscell.Value);
                            }
                            else
                            {
                                //cprc = 6;
                                currentcell = subpage.wprc.Cells[cprr, cprc];
                                currrow = currentcell.Row;
                                previouscell = subpage.wprp.Cells[pprr, cprc];
                                prevrow = previouscell.Row;
                                current = Convert.ToString(currentcell.Value);
                                previous = Convert.ToString(previouscell.Value);
                            }

                           
                                //string na = subpage.wprc.Cells[cprr, cprc].Value;
                            if (current != null)
                            {
                                int nal = current.Length;
                                if (nal > 5)
                                {
                                    string subna = current.Substring(current.Length - 4);
                                    if (subna == "1111")
                                    {
                                        currentcell.Value = "N/A";
                                        current = Convert.ToString(currentcell.Value);
                                    }
                                }
                                else if (current == "n/a" || current == "na")
                                {
                                    currentcell.Value = "N/A";
                                    current = Convert.ToString(currentcell.Value);
                                }
                            }
                                                       
                            
                            //if (currentcell.Value is DateTime && previouscell.Value is string)
                            //{
                            //    currentcell.Interior.Color = Color.Goldenrod;                                
                            //}
                            //else if (currentcell.Value is string && previouscell.Value is DateTime)
                            //{
                            //    currentcell.Interior.Color = Color.Goldenrod;                                
                            //}
                            if (current != null)
                            {
                                if (current != previous && cprc >= 8)
                                {
                                    currentcell.Interior.Color = Color.Goldenrod;
                                }
                            }
                        }
                        break;
                    }
                    else if (cprr == lastrow - 1 && foundid == false) //cprr == lastrow - 1 && 
                    {
                        if (subpage.wprc.Range["A" + cprr].Value > 0)
                        {
                            subpage.wprc.Range["A" + cprr + ":" + "N" + cprr].Interior.Color = Color.Goldenrod;
                            break;
                        }
                        else
                        {
                            subpage.wprc.Range["G" + cprr + ":" + "N" + cprr].Interior.Color = Color.Goldenrod;
                            break;
                        }
                    }
                }
            }
        }

        private void stoplightwprc()
        {
            lastrow = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;
            lastcol = subpage.wprc.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Column;

            for (int i = 2; i < lastrow; i++)
            {
                for (int c = 8; c < lastcol; c++)
                {
                    if (subpage.wprc.Cells[i, c].Value is DateTime)
                    {

                        if (c == 9 || c == 11)
                        {
                            continue;
                        }

                        if (c == 8 || c == 10)
                        {
                            if (subpage.wprc.Cells[i, c].Value <= days[0] && !(subpage.wprc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range r = subpage.wprc.Cells[i, c + 1];                                
                                r.Interior.Color = Color.Red;
                            }

                            else if (subpage.wprc.Cells[i, c].Value == days[1] && !(subpage.wprc.Cells[i, c + 1].Value is DateTime))
                            {
                                Excel.Range p = subpage.wprc.Cells[i, c];
                                p.Interior.Color = Color.HotPink;
                            }

                            else if (((subpage.wprc.Cells[i, c].Value == days[2] && !(subpage.wprc.Cells[i, c + 1].Value is DateTime)) || (subpage.wprc.Cells[i, c].Value == days[3]) && !(subpage.wprc.Cells[i, c + 1].Value is DateTime)))
                            {
                                Excel.Range p = subpage.wprc.Cells[i, c];
                                p.Interior.Color = Color.Yellow;
                            }

                            else if (((subpage.wprc.Cells[i, c].Value == days[4] && !(subpage.wprc.Cells[i, c + 1].Value is DateTime)) || (subpage.wprc.Cells[i, c].Value == days[5]) && !(subpage.wprc.Cells[i, c + 1].Value is DateTime)))
                            {
                                Excel.Range p = subpage.wprc.Cells[i, c];
                                p.Interior.Color = Color.LimeGreen;
                            }
                        }

                        //else
                        //{
                        //    if ((DateTime)subpage.wprc.Cells[i, c].Value == days[1])
                        //    {
                        //        Excel.Range p = subpage.wprc.Cells[i, c + 1];
                        //        p.Interior.Color = Color.HotPink;
                        //    }

                        //    else if ((subpage.wprc.Cells[i, c].Value == days[2] || subpage.wprc.Cells[i, c].Value == days[3]))
                        //    {
                        //        Excel.Range p = subpage.wprc.Cells[i, c + 1];
                        //        p.Interior.Color = Color.Yellow;
                        //    }

                        //    else if ((subpage.wprc.Cells[i, c].Value == days[4] || subpage.wprc.Cells[i, c].Value == days[5]))
                        //    {
                        //        Excel.Range p = subpage.wprc.Cells[i, c + 1];
                        //        p.Interior.Color = Color.LimeGreen;
                        //    }
                        //}
                    }
                }
            }
            subpage.xlpr.ScreenUpdating = true;
            subpage.xlpr.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
            
        }

        private void shutdown()
        {       
            //subpage.wbpr.Save();
            wbs.Save();
            //subpage.wbpr.Close();
            wbs.Close();
            Marshal.FinalReleaseComObject(del);
            //Marshal.FinalReleaseComObject(currentcell);
            //Marshal.FinalReleaseComObject(previouscell);
            Marshal.FinalReleaseComObject(wsc);
            Marshal.FinalReleaseComObject(wsp);
            Marshal.FinalReleaseComObject(wbs);
            //subpage.xlpr.Application.Quit();
            xls.Application.Quit();
            Marshal.FinalReleaseComObject(xls);
            //subpage.xlpr.Quit();
            //xls.Quit();
            Application.Exit();            
        }
    }
}
